package com.gxa.shop.dao.product.impl;

import com.gxa.shop.bean.dto.ProductQueryPage;
import com.gxa.shop.bean.pojo.Carousel;
import com.gxa.shop.bean.pojo.Product;
import com.gxa.shop.bean.vo.ProductOption;
import com.gxa.shop.bean.vo.ProductVo;
import com.gxa.shop.commons.jdbc.JDBCUtil;
import com.gxa.shop.dao.product.ProductDao;


import java.util.*;

/**
 * com.gxa.shop.bean.dao.impl
 * User: zjy
 * Date: 2024/12/19 14:41
 * motto:   逆水行舟不进则退
 * Description:
 * Version: V1.0
 */
public class ProductDaoImpl implements ProductDao {

    @Override
    public int insert(Product product) {
        String  sql="insert  product(category_id,brand_id,product_name,unit_price,product_image,product_color,product_size,product_material,weight,stock,sales,launch_time,discount_id) value (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        JDBCUtil  jdbcUtil=new JDBCUtil();
        int update = jdbcUtil.update(sql,
                product.getCategoryId(),
                product.getBrandId(),
                product.getProductName(),
                product.getUnitPrice(),
                product.getProductImage(),
                product.getProductColor(),
                product.getProductSize(),
                product.getProductMaterial(),
                product.getWeight(),
                product.getStock(),
                product.getSales(),
                product.getLaunchTime(),
                product.getDiscountId()
                );
        return update;
    }

    @Override
    public int update(Product product) {
        String  sql="update product set  category_id=?,brand_id=?,product_name=?,unit_price=?,product_image=?,product_color=?,product_size=?,product_material=?,weight=?,stock=?,sales=?,launch_time=?,discount_id=? where product_id=?";
        JDBCUtil  jdbcUtil=new JDBCUtil();
        int update = jdbcUtil.update(sql, product.getCategoryId(),product.getBrandId(),product.getProductName(),product.getUnitPrice(),product.getProductImage(),product.getProductColor(),product.getProductSize(),product.getProductMaterial(),product.getWeight(),product.getStock(),product.getSales(),product.getLaunchTime(),product.getDiscountId(),product.getProductId());
        return update;
    }

    @Override
    public int deleteById(Integer productId) {
        String  sql="delete  from  product  where  product_id=?";
        JDBCUtil  jdbcUtil=new JDBCUtil();
        int update = jdbcUtil.update(sql,productId);
        return update;
    }

    @Override
    public String getProductImageByProductId(String productId) {
        String imagePath = null;

        // SQL 查询语句
        String sql = "SELECT product_image FROM product WHERE product_id = ?";

        // 使用 JDBCUtil 获取数据库连接并执行查询
        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> result = jdbcUtil.query(sql, productId);

        // 检查结果集并提取图片路径
        if (!result.isEmpty()) {
            Map<String, Object> row = result.get(0); // 假设brand_id是唯一的，只取第一个结果
            imagePath = (String) row.get("product_image");
        }

        // 返回图片路径
        return imagePath;
    }

    @Override
    public void updateProductImage(String productId, String imagePath) {
        String sql = "UPDATE product SET product_image = ?,category_id=?,brand_id=?,product_name=?,unit_price=?,product_color=?,product_size=?,product_material=?,weight=?,stock=?,sales=?,launch_time=?,discount_id=?WHERE product_id = ?";

        // 使用 JDBCUtil 执行更新操作
        JDBCUtil jdbcUtil = new JDBCUtil();
        int resultRows = jdbcUtil.update(sql, imagePath, productId);

        // 检查更新结果（可选）
        if (resultRows > 0) {
            System.out.println("图片路径更新成功！");
        } else {
            System.out.println("没有找到对应的产品ID，更新失败！");
        }

    }

    @Override
    public Long findByTotal(ProductQueryPage productQueryPage) {
        // 初始化SQL语句和参数列表
        StringBuffer sql = new StringBuffer(
                "SELECT COUNT(p.product_id) AS ct " +
                        "FROM product p " +
                        "LEFT JOIN category c ON p.category_id = c.category_id " +
                        "LEFT JOIN brand b ON p.brand_id = b.brand_id " +
                        "LEFT JOIN discount d ON p.discount_id = d.discount_id " +
                        "WHERE 1=1 "
        );

        List<Object> parameters = new ArrayList<>();

        // 动态添加查询条件
        if (productQueryPage.getProductName() != null && !productQueryPage.getProductName().trim().isEmpty()) {
            sql.append(" AND p.product_name LIKE CONCAT('%', ?, '%') ");
            parameters.add(productQueryPage.getProductName());
        }

        if (productQueryPage.getProductColor() != null && !productQueryPage.getProductColor().trim().isEmpty()) {
            sql.append(" AND p.product_color LIKE CONCAT('%', ?, '%') ");
            parameters.add(productQueryPage.getProductColor());
        }

        if (productQueryPage.getProductMaterial() != null && !productQueryPage.getProductMaterial().trim().isEmpty()) {
            sql.append(" AND p.product_material LIKE CONCAT('%', ?, '%') ");
            parameters.add(productQueryPage.getProductMaterial());
        }

        if (productQueryPage.getCategoryName() != null && !productQueryPage.getCategoryName().trim().isEmpty()) {
            sql.append(" AND c.category_name LIKE CONCAT('%', ?, '%') ");
            parameters.add(productQueryPage.getCategoryName());
        }

        if (productQueryPage.getBrandName() != null && !productQueryPage.getBrandName().trim().isEmpty()) {
            sql.append(" AND b.brand_name LIKE CONCAT('%', ?, '%') ");
            parameters.add(productQueryPage.getBrandName());
        }

        // 打印调试信息
        System.out.println("SQL: " + sql);
        System.out.println("Parameters: " + parameters);

        JDBCUtil jdbcUtil = new JDBCUtil();
        try {
            List<Map<String, Object>> query = jdbcUtil.query(sql.toString(), parameters.toArray());
            if (!query.isEmpty()) {
                // 注意：COUNT的结果可能是BigDecimal或其他类型，这里假设为Long
                return ((Number) query.get(0).get("ct")).longValue();
            } else {
                return 0L;
            }
        } catch (Exception e) {
            // 更好的错误处理和日志记录
            e.printStackTrace();
            System.err.println("查询分类总数时发生错误: " + e.getMessage());
            // 在实际应用中应使用日志框架进行记录
            return 0L;
        }
    }


    @Override
    public List<ProductOption> findByPage(ProductQueryPage productQueryPage) {
        // 初始化SQL语句和参数列表
        StringBuffer sql = new StringBuffer(
                "SELECT " +
                        "    p.product_id, " +
                        "    c.category_name, " +
                        "    b.brand_name, " +
                        "    b.brand_image, " +
                        "    p.product_name, " +
                        "    p.unit_price, " + // 注意这里应该是 unit_price 而不是 unitPrice
                        "    p.product_image, " +
                        "    p.product_color, " +
                        "    p.product_size, " +
                        "    p.product_material, " +
                        "    p.weight, " +
                        "    p.stock, " +
                        "    p.sales, " +
                        "    p.launch_time, " +
                        "    d.discount_rate, " +
                        "    d.discount_name " +
                        "FROM product p " +
                        "LEFT JOIN category c ON p.category_id = c.category_id " +
                        "LEFT JOIN brand b ON p.brand_id = b.brand_id " +
                        "LEFT JOIN discount d ON p.discount_id = d.discount_id " +
                        "WHERE 1=1 "
        );

        List<Object> parameters = new ArrayList<>();

        // 动态添加查询条件
        if (productQueryPage.getProductName() != null && !productQueryPage.getProductName().trim().isEmpty()) {
            sql.append(" AND p.product_name LIKE CONCAT('%', ?, '%') ");
            parameters.add(productQueryPage.getProductName());
        }

        if (productQueryPage.getProductColor() != null && !productQueryPage.getProductColor().trim().isEmpty()) {
            sql.append(" AND p.product_color LIKE CONCAT('%', ?, '%') ");
            parameters.add(productQueryPage.getProductColor());
        }

        if (productQueryPage.getProductMaterial() != null && !productQueryPage.getProductMaterial().trim().isEmpty()) {
            sql.append(" AND p.product_material LIKE CONCAT('%', ?, '%') ");
            parameters.add(productQueryPage.getProductMaterial());
        }

        if (productQueryPage.getCategoryName() != null && !productQueryPage.getCategoryName().trim().isEmpty()) {
            sql.append(" AND c.category_name LIKE CONCAT('%', ?, '%') ");
            parameters.add(productQueryPage.getCategoryName());
        }

        if (productQueryPage.getBrandName() != null && !productQueryPage.getBrandName().trim().isEmpty()) {
            sql.append(" AND b.brand_name LIKE CONCAT('%', ?, '%') ");
            parameters.add(productQueryPage.getBrandName());
        }

        // 添加分页
        sql.append(" ORDER BY p.product_id ASC ");

        // 使用参数化查询来设置分页参数
        int offset = (productQueryPage.getPage() - 1) * productQueryPage.getLimit();
        sql.append(" LIMIT ?, ? ");
        parameters.add(offset);
        parameters.add(productQueryPage.getLimit());

        // 打印调试信息
        System.out.println("SQL: " + sql);
        System.out.println("Parameters: " + parameters);

        JDBCUtil jdbcUtil = new JDBCUtil();
        try {
            List<Map<String, Object>> query = jdbcUtil.query(sql.toString(), parameters.toArray());
            List<ProductOption> list = new ArrayList<>();
            for (Map<String, Object> row : query) {
                ProductOption product = new ProductOption();
                product.setProductId((Integer) row.get("product_id"));
                product.setCategoryName((String) row.get("category_name"));
                product.setBrandName((String) row.get("brand_name"));
                product.setBrandImage((String) row.get("brand_image"));
                product.setProductName((String) row.get("product_name"));
                product.setUnitPrice((Double) row.get("unit_price")); // 注意字段名匹配
                product.setProductImage((String) row.get("product_image"));
                product.setProductColor((String) row.get("product_color"));
                product.setProductSize((String) row.get("product_size"));
                product.setProductMaterial((String) row.get("product_material"));
                product.setWeight((Double) row.get("weight"));
                product.setStock((Integer) row.get("stock"));
                product.setSales((Integer) row.get("sales"));
                product.setLaunchTime((Date) row.get("launch_time"));
               product.setDiscountRate((Double) row.get("discount_rate"));
                product.setDiscountName((String) row.get("discount_name"));
                list.add(product);
            }
            return list;
        } catch (Exception e) {
            // 更好的错误处理和日志记录
            e.printStackTrace();
            System.err.println("查询分页产品时发生错误: " + e.getMessage());
            // 在实际应用中应使用日志框架进行记录
            return Collections.emptyList(); // 返回空列表而不是null
        }
    }

    @Override
    public List<ProductVo> queryByCategory(Integer categoryId) {
        String sql ="SELECT p.product_id, c.category_name, b.brand_name,p.product_name,p.unit_price,p.product_image,p.product_color,p.product_size,p.product_material,p.weight,p.stock,p.sales,p.launch_time,d.discount_name,d.discount_rate FROM product p JOIN category c ON p.category_id = c.category_id JOIN brand b ON p.brand_id = b.brand_id LEFT JOIN discount d ON p.discount_id = d.discount_id WHERE p.category_id = ? ORDER BY p.product_id";
        JDBCUtil jdbcUtil = new JDBCUtil();
        try {
            List<Map<String, Object>> query = jdbcUtil.query(sql.toString(),categoryId);
            if(query.size()>0){
                List<ProductVo> productVos = new ArrayList<>();
                for (Map<String, Object> row : query) {
                    ProductVo productVo = new ProductVo();
                    productVo.setProductId((Integer) row.get("product_id"));
                    productVo.setCategoryName((String) row.get("category_name"));
                    productVo.setBrandName((String) row.get("brand_name"));
                    productVo.setProductName((String) row.get("product_name"));
                    productVo.setUnitPrice((Double) row.get("unit_price"));
                    productVo.setProductImage((String) row.get("product_image"));
                    productVo.setProductColor((String) row.get("product_color"));
                    productVo.setProductSize((String) row.get("product_size"));
                    productVo.setProductMaterial((String) row.get("product_material"));
                    productVo.setStock((Integer) row.get("stock"));
                    productVo.setSales((Integer) row.get("sales"));
                    productVo.setDiscountName((String) row.get("discount_name"));
                    productVo.setDiscountRate((Double) row.get("discount_rate"));
                    productVos.add(productVo);
                }
                return  productVos;
            }else {
                return Collections.emptyList();
            }
        }catch (Exception e) {
            e.printStackTrace();
            System.err.println("分类查询产品时发生错误: " + e.getMessage());
            // 在实际应用中应使用日志框架进行记录
            return Collections.emptyList(); // 返回空列表而不是null
        }
    }

    @Override
    public ProductVo queryById(String productId) {
        String sql ="SELECT p.product_id, c.category_name, b.brand_name,p.product_name,p.unit_price,p.product_image,p.product_color,p.product_size,p.product_material,p.weight,p.stock,p.sales,p.launch_time,d.discount_name,d.discount_rate FROM product p LEFT JOIN category c ON p.category_id = c.category_id LEFT JOIN brand b ON p.brand_id = b.brand_id LEFT JOIN discount d ON p.discount_id = d.discount_id WHERE p.product_id = ?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql.toString(),productId);
        if(query.size()>0){
            ProductVo productVo = new ProductVo();
            productVo.setProductId((Integer) query.get(0).get("product_id"));
            productVo.setCategoryName((String) query.get(0).get("category_name"));
            productVo.setBrandName((String) query.get(0).get("brand_name"));
            productVo.setProductName((String) query.get(0).get("product_name"));
            productVo.setUnitPrice((Double) query.get(0).get("unit_price"));
            productVo.setProductImage((String) query.get(0).get("product_image"));
            productVo.setProductColor((String) query.get(0).get("product_color"));
            productVo.setProductSize((String) query.get(0).get("product_size"));
            productVo.setProductMaterial((String) query.get(0).get("product_material"));
            productVo.setStock((Integer) query.get(0).get("stock"));
            productVo.setSales((Integer) query.get(0).get("sales"));
            productVo.setDiscountName((String) query.get(0).get("discount_name"));
            productVo.setDiscountRate((Double) query.get(0).get("discount_rate"));
            return  productVo;
        }else {
            return null;
        }
    }

    @Override
    public List<ProductVo> findByName(String name) {
        String sql="select product_id, product_name from product where product_name LIKE CONCAT('%', ?, '%') ";
        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql,name);
        List<ProductVo> productVoList=new ArrayList<>();
        for (Map<String, Object> map: query
             ) {
            ProductVo productVo = new ProductVo();
            productVo.setProductId((Integer) map.get("product_id"));
            productVo.setProductName((String) map.get("product_name") );
            productVoList.add(productVo);
        }
        return productVoList;
    }
}
